package kafeisc.dao;


import kafeisc.domain.Order;
import kafeisc.domain.User;
import kafeisc.utils.DataSourceUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Classname OrderDao
 * @Description TODO
 * @Date 20:53
 * @Created 
 */
public class OrderDao {
   /**
    *  生成订单
    * @param order
    * @throws SQLException
    */
   public void addProduct(Order order) throws SQLException {
      // 1.生成Sql语句
      String sql = "insert into orders(id,money,receiverAddress,receiverName,receiverPhone,paystate,user_id) values(?,?,?,?,?,?,?)";
      // 2.生成执行sql语句的QueryRunner,不传递参数
      QueryRunner runner = new QueryRunner();
      // 3.执行update()方法插入数据
      runner.update(DataSourceUtils.getConnection(), sql, order.getId(),
              order.getMoney(), order.getReceiverAddress(), order
                      .getReceiverName(), order.getReceiverPhone(), order.getPaystate() , order
                      .getUser().getId());
   }
   /**
    * 查找用户所属订单
    */
   public List<Order> findOrderByUser(final User user) throws SQLException {
      String sql = "select * from orders where user_id=?";
      QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
      return runner.query(sql, new ResultSetHandler<List<Order>>() {
         public List<Order> handle(ResultSet rs) throws SQLException {
            List<Order> orders = new ArrayList<Order>();
            while (rs.next()) {
               Order order = new Order();
               order.setId(rs.getString("id"));
               order.setMoney(rs.getDouble("money"));
               order.setOrdertime(rs.getDate("ordertime"));
               order.setPaystate(rs.getInt("paystate"));
               order.setReceiverAddress(rs.getString("receiverAddress"));
               order.setReceiverName(rs.getString("receiverName"));
               order.setReceiverPhone(rs.getString("receiverPhone"));
               order.setUser(user);
               orders.add(order);
            }
            return orders;
         }
      }, user.getId());
   }
   /**
    * 根据id查找订单信息
    * @param id
    * @return
    * @throws SQLException
    */
   public Order findOrderById(String id) throws SQLException {
      String sql = "select * from orders,user where orders.user_id=user.id and orders.id=?";
      QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
      return runner.query(sql, new ResultSetHandler<Order>() {
         public Order handle(ResultSet rs) throws SQLException {
            Order order = new Order();
            while (rs.next()) {
               order.setId(rs.getString("orders.id"));
               order.setMoney(rs.getDouble("orders.money"));
               order.setOrdertime(rs.getDate("orders.ordertime"));
               order.setPaystate(rs.getInt("orders.paystate"));
               order.setReceiverAddress(rs.getString("orders.receiverAddress"));
               order.setReceiverName(rs.getString("orders.receiverName"));
               order.setReceiverPhone(rs.getString("orders.receiverPhone"));

               User user = new User();
               user.setId(rs.getInt("user.id"));
               user.setEmail(rs.getString("user.email"));
               user.setGender(rs.getString("user.gender"));
               user.setActiveCode(rs.getString("user.activecode"));
               user.setIntroduce(rs.getString("user.introduce"));
               user.setPassword(rs.getString("user.password"));
               user.setRegistTime(rs.getDate("user.registtime"));
               user.setRole(rs.getString("user.role"));
               user.setState(rs.getInt("user.state"));
               user.setTelephone(rs.getString("user.telephone"));
               user.setUsername(rs.getString("user.username"));
               order.setUser(user);
            }
            return order;
         }
      }, id);
   }
   /**
    *  查找所有订单
    * @return
    * @throws SQLException
    */
   public List<Order> findAllOrder() throws SQLException {
      //1.创建sql
      String sql = "select orders.*,user.* from orders,user where user.id=orders.user_id order by orders.user_id";
      //2.创建QueryRunner对象
      QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
      //3.返回QueryRunner对象query()方法的查询结果
      return runner.query(sql, new ResultSetHandler<List<Order>>() {
         public List<Order> handle(ResultSet rs) throws SQLException {
            //创建订单集合
            List<Order> orders = new ArrayList<Order>();
            //循环遍历订单和用户信息
            while (rs.next()) {
               Order order = new Order();
               order.setId(rs.getString("orders.id"));
               order.setMoney(rs.getDouble("orders.money"));
               order.setOrdertime(rs.getDate("orders.ordertime"));
               order.setPaystate(rs.getInt("orders.paystate"));
               order.setReceiverAddress(rs.getString("orders.receiverAddress"));
               order.setReceiverName(rs.getString("orders.receiverName"));
               order.setReceiverPhone(rs.getString("orders.receiverPhone"));
               orders.add(order);

               User user = new User();
               user.setId(rs.getInt("user.id"));
               user.setEmail(rs.getString("user.email"));
               user.setGender(rs.getString("user.gender"));
               user.setActiveCode(rs.getString("user.activecode"));
               user.setIntroduce(rs.getString("user.introduce"));
               user.setPassword(rs.getString("user.password"));
               user.setRegistTime(rs.getDate("user.registtime"));
               user.setRole(rs.getString("user.role"));
               user.setState(rs.getInt("user.state"));
               user.setTelephone(rs.getString("user.telephone"));
               user.setUsername(rs.getString("user.username"));
               order.setUser(user);
            }
            return orders;
         }
      });
   }
   /**
    *  根据订单号修改订单状态
    * @param id
    * @throws SQLException
    */
   public void updateOrderState(String id) throws SQLException {
      String sql = "update orders set paystate=1 where id=?";
      QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
      runner.update(sql, id);
      System.out.println(runner.update(sql, id));
   }
   /**
    *  多条件查询
    * @param id
    * @param receiverName
    * @return
    * @throws SQLException
    */
   public List<Order> findOrderByManyCondition(String id, String receiverName)
           throws SQLException {
      //1.创建集合对象
      List<Object> objs = new ArrayList<Object>();
      //2.定义查询sql
      String sql = "select orders.*,user.* from orders,user where user.id=orders.user_id ";
      //3.根据参数拼接sql语句
      if (id != null && id.trim().length() > 0) {
         sql += " and orders.id=?";
         objs.add(id);
      }
      if (receiverName != null && receiverName.trim().length() > 0) {
         sql += " and receiverName=?";
         objs.add(receiverName);
      }
      sql += " order by orders.user_id";
      //4.创建QueryRunner对象
      QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
      //5.返回QueryRunner对象query方法的执行结果
      return runner.query(sql, new ResultSetHandler<List<Order>>() {
         public List<Order> handle(ResultSet rs) throws SQLException {
            List<Order> orders = new ArrayList<Order>();
            //循环遍历出订单和用户信息
            while (rs.next()) {
               Order order = new Order();
               order.setId(rs.getString("orders.id"));
               order.setMoney(rs.getDouble("orders.money"));
               order.setOrdertime(rs.getDate("orders.ordertime"));
               order.setPaystate(rs.getInt("orders.paystate"));
               order.setReceiverAddress(rs
                       .getString("orders.receiverAddress"));
               order.setReceiverName(rs.getString("orders.receiverName"));
               order.setReceiverPhone(rs.getString("orders.receiverPhone"));
               orders.add(order);
               User user = new User();
               user.setId(rs.getInt("user.id"));
               user.setEmail(rs.getString("user.email"));
               user.setGender(rs.getString("user.gender"));
               user.setActiveCode(rs.getString("user.activecode"));
               user.setIntroduce(rs.getString("user.introduce"));
               user.setPassword(rs.getString("user.password"));
               user.setRegistTime(rs.getDate("user.registtime"));
               user.setRole(rs.getString("user.role"));
               user.setState(rs.getInt("user.state"));
               user.setTelephone(rs.getString("user.telephone"));
               user.setUsername(rs.getString("user.username"));
               order.setUser(user);

            }

            return orders;
         }
      }, objs.toArray());
   }
   /**
    * 根据id删除订单
    * @param id
    * @throws SQLException
    */
   public void delOrderById(String id) throws SQLException {
      String sql="delete from orders where id=?";
      QueryRunner runner = new QueryRunner();
      runner.update(DataSourceUtils.getConnection(),sql,id);
   }
}
